﻿Module mdlCRUD
    Public blnFlag As Boolean = True

    Public Sub loadRecord(ByVal dtsRecords As DataSet, ByVal strName As String, ByVal strTable As String, ByVal strCondicion As String, ByVal strOrderby As String)
        Try
            mdlConexion.conexion() 'Realiza conexion con Base de Datos
            idcon.Open()

            Dim strSql As String = "SELECT * FROM " & strTable & IIf(Trim(strCondicion) <> "", " WHERE " & strCondicion, "") & IIf(Trim(strOrderby) <> "", " ORDER BY " & strOrderby, "")
            Dim odbcAdapter As New Odbc.OdbcDataAdapter(strSql, idcon)

            odbcAdapter.Fill(dtsRecords.Tables(strName))

            idcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
        End Try
    End Sub

    Public Sub insRecord(ByVal strTable As String, ByVal arrFields As Array, ByVal arrValues As Array)
        Dim strCampos As String = ""
        Dim strValor As String = ""
        Dim strSQL As String = ""

        For Each value In arrFields
            strCampos &= IIf(Trim(strCampos) <> "", ", ", "") & value
        Next

        For Each value In arrValues
            strValor &= IIf(Trim(strValor) <> "", ", ", "") & IIf(Trim(value) <> "", "'" & value & "'", "null")
        Next

        strSQL = "INSERT INTO " & strTable & " (" & strCampos & ") VALUES (" & strValor & ")"

        Try
            mdlConexion.conexion() 'Realiza conexion con Base de Datos
            idcon.Open()


            Dim odbcAdapter As New Odbc.OdbcDataAdapter
            Dim cmdCommand As New Odbc.OdbcCommand(strSQL, idcon)

            odbcAdapter.InsertCommand = cmdCommand
            odbcAdapter.InsertCommand.ExecuteNonQuery()

            MsgBox("Grabado Exitoso", MsgBoxStyle.Information, "CRC")
            blnFlag = True

            idcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
            blnFlag = False
        End Try
    End Sub

    Public Sub updRecord(ByVal strTable As String, ByVal arrParametros As Array, ByVal strCondicion As String)
        Dim strCampos As String = ""
        Dim strSQL As String = ""

        For Each value In arrParametros
            strCampos &= IIf(Trim(strCampos) <> "", ", ", "") & value
        Next

        strSQL = "UPDATE " & strTable & " SET " & strCampos & " WHERE " & strCondicion

        Try
            mdlConexion.conexion() 'Realiza conexion con Base de Datos
            idcon.Open()

            Dim odbcAdapter As New Odbc.OdbcDataAdapter
            Dim cmdCommand As New Odbc.OdbcCommand(strSQL, idcon)

            odbcAdapter.InsertCommand = cmdCommand
            odbcAdapter.InsertCommand.ExecuteNonQuery()

            MsgBox("Grabado Exitoso", MsgBoxStyle.Information, "CRC")
            blnFlag = True

            idcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
            blnFlag = False
        End Try
    End Sub

    Public Sub delRecord(ByVal strTable As String, ByVal strCondicion As String)
        Dim strSQL As String = "DELETE FROM " & strTable & " WHERE " & strCondicion

        Try
            mdlConexion.conexion() 'Realiza conexion con Base de Datos
            idcon.Open()

            Dim odbcAdapter As New Odbc.OdbcDataAdapter
            Dim cmdCommand As New Odbc.OdbcCommand(strSQL, idcon)

            odbcAdapter.InsertCommand = cmdCommand
            odbcAdapter.InsertCommand.ExecuteNonQuery()

            MsgBox("Se elimino el registro seleccionado", MsgBoxStyle.Information, "Editar")
            blnFlag = True

            idcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
            blnFlag = False
        End Try
    End Sub

    Public Sub sqlRecord(ByVal dtsRecords As DataSet, ByVal strName As String, ByVal strSql As String)
        Try
            mdlConexion.conexion() 'Realiza conexion con Base de Datos
            idcon.Open()

            Dim odbcAdapter As New Odbc.OdbcDataAdapter(strSql, idcon)
            odbcAdapter.Fill(dtsRecords.Tables(strName))

            idcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
        End Try
    End Sub

    'Proceso para WSIN
    Public Sub insRecordnomsg(ByVal strTable As String, ByVal arrFields As Array, ByVal arrValues As Array)
        Dim strCampos As String = ""
        Dim strValor As String = ""
        Dim strSQL As String = ""

        For Each value In arrFields
            strCampos &= IIf(Trim(strCampos) <> "", ", ", "") & value
        Next

        For Each value In arrValues
            strValor &= IIf(Trim(strValor) <> "", ", ", "") & IIf(Trim(value) <> "", "'" & value & "'", "null")
        Next

        strSQL = "INSERT INTO " & strTable & " (" & strCampos & ") VALUES (" & strValor & ")"

        Try
            mdlConexion.conexion() 'Realiza conexion con Base de Datos
            idcon.Open()


            Dim odbcAdapter As New Odbc.OdbcDataAdapter
            Dim cmdCommand As New Odbc.OdbcCommand(strSQL, idcon)

            odbcAdapter.InsertCommand = cmdCommand
            odbcAdapter.InsertCommand.ExecuteNonQuery()
            blnFlag = True

            idcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
            blnFlag = False
        End Try
    End Sub

    Public Sub updRecordnomsg(ByVal strTable As String, ByVal arrParametros As Array, ByVal strCondicion As String)
        Dim strCampos As String = ""
        Dim strSQL As String = ""

        For Each value In arrParametros
            strCampos &= IIf(Trim(strCampos) <> "", ", ", "") & value
        Next

        strSQL = "UPDATE " & strTable & " SET " & strCampos & " WHERE " & strCondicion

        Try
            mdlConexion.conexion() 'Realiza conexion con Base de Datos
            idcon.Open()

            Dim odbcAdapter As New Odbc.OdbcDataAdapter
            Dim cmdCommand As New Odbc.OdbcCommand(strSQL, idcon)

            odbcAdapter.InsertCommand = cmdCommand
            odbcAdapter.InsertCommand.ExecuteNonQuery()

            blnFlag = True

            idcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
            blnFlag = False
        End Try
    End Sub

    Function getConsecutivo(ByVal strTable As String, ByVal strCampopk As String, ByVal intId As Integer)
        Dim dstRecordCC As New DataSet
        Dim dstRecord As New DataSet
        Dim blnNuevoId As Boolean = False
        Dim intConsecutivo As Integer = 0

        'Crea el concepto del DataSet
        dstRecordCC.Tables.Add("Consecutivo")


        'Consulta el valor consecutivo a raiz de una id que marca el consecutivo
        mdlCRUD.loadRecord(dstRecordCC, "Consecutivo", "consecutivo", "consecutivo_id = '" & intId & "'", Nothing)

        If dstRecordCC.Tables("Consecutivo").Rows.Count > 0 Then
            Dim arrRow As Array
            arrRow = dstRecordCC.Tables("Consecutivo").Rows(0).ItemArray.ToArray

            intConsecutivo = arrRow(2).ToString
        End If


        'Recorre el listado de la tabla para realizar la busqueda del proximo valor consecutivo
        Do While blnNuevoId = False
            intConsecutivo += 1
            dstRecord = New DataSet
            dstRecord.Tables.Add(strTable)
            mdlCRUD.loadRecord(dstRecord, strTable, strTable, strCampopk & " = '" & intConsecutivo & "'", Nothing)

            If dstRecord.Tables(strTable).Rows.Count = 0 Then
                blnNuevoId = True
            End If
        Loop

        Return intConsecutivo
    End Function
End Module
